from time import *
import requests
import pymysql
from DBUtils.PooledDB import PooledDB
import threading
import pandas as pd
ID = "ww647f8e785f4d83d5"
SECRET = "9lIe12ay42TwCw4glRIDyuzKdWn70jgGjXE0NkcGdYc"
url_get_access_token = "https://qyapi.weixin.qq.com/cgi-bin/gettoken?corpid=" + ID + "&corpsecret=" + SECRET

#获取access_token#
def get_access_token(ID,SECRET):
    access_token_json = requests.get(url_get_access_token).json()
    access_token = access_token_json['access_token']
    return access_token

def pool():
    pool = PooledDB(pymysql, 15, host='localhost', user='root', passwd='zcxvbmn', db='weixindb', port=3306)
    return pool


url_get_member_list = "https://qyapi.weixin.qq.com/cgi-bin/user/simplelist?access_token=" + get_access_token(ID, SECRET) + "&department_id=1&fetch_child=1"

def clean_client_detail_list():
    db = pool().connection()
    cursor = db.cursor()
    clean_client_count_sql = "delete from client_detail_list_chongqing"
    cursor.execute(clean_client_count_sql)
    db.commit()
    return

url_get_client_detail_list = "https://qyapi.weixin.qq.com/cgi-bin/externalcontact/batch/get_by_user?access_token=" + get_access_token(ID, SECRET)

def get_member_list():
    member_list = requests.get(url_get_member_list).json()['userlist']
    return member_list

def get_client_detail_list(userid):
    db = pool().connection()
    cursor = db.cursor()
    i = 1  # i为计数器
    next_cursor = ''
    while i == 1 or next_cursor != '':
        user = {
            "userid": userid,
            "limit": 100,
            "cursor": next_cursor,
        }
        access_token_json = requests.get(url_get_access_token).json()
        access_token = access_token_json['access_token']
        url_get_client_detail_list = "https://qyapi.weixin.qq.com/cgi-bin/externalcontact/batch/get_by_user" \
                                     "?access_token=" + access_token
        client_detail_list_json = requests.post(url_get_client_detail_list, json=user).json()
        external_contact_list = client_detail_list_json['external_contact_list']
        client_detail_list = []
        insert_sql = ""
        for external_contact in external_contact_list:
            member_userid = (external_contact['follow_info']['userid'],)
            createtime = (repr(external_contact['follow_info']['createtime']),)
            try:
                add_way = (repr(external_contact['follow_info']['add_way']),)
            except:
                add_way = ('',)
            external_userid = (external_contact['external_contact']['external_userid'],)
            type = (repr(external_contact['external_contact']['type']),)
            gender = (repr(external_contact['external_contact']['gender']),)
            tuple_client_detail = member_userid + createtime + add_way + external_userid + type + gender
            insert_sql = "insert into client_detail_list_chongqing(userid,createtime,add_way,external_userid,type,gender) values(%s,%s,%s,%s,%s,%s)"
            client_detail_list.append(tuple_client_detail)
        # 这里要开始尝试用executemany一次插入100条sql#
        cursor.executemany(insert_sql, client_detail_list)
        db.commit()
        try:
            next_cursor = client_detail_list_json['next_cursor']
        except:
            print(client_detail_list_json)
            get_access_token(ID, SECRET)
        i = i + 1
    return

def task(start_num, end_num):
    for member in get_member_list()[start_num: end_num]:
        userid = member['userid']
        get_client_detail_list(userid)
    return

def get_client_list_threading_task():
    threads = []
    for n in range(0, len(get_member_list()), 100):
        start_num = n
        end_num = n+99
        task_threading = threading.Thread(target=task, args=(start_num, end_num))
        threads.append(task_threading)
        task_threading.start()
    print("多线程拉取顾客详情中...")
    for task_threading in threads:
        task_threading.join()
    print("拉取顾客明细线程结束")
    return

# 展示所有的列
pd.set_option('display.max_columns', None)

# 将顾客信息明细表转为dataframe
def get_client_detail_df(client_table_name):
    db = pymysql.connect(host="localhost", user="root", password="zcxvbmn", database="weixindb")
    cursor = db.cursor()
    sql = "select * from %s" % client_table_name
    cursor.execute(sql)
    client_detail = cursor.fetchall()
    client_detail_df = pd.DataFrame(list(client_detail), columns=['id', 'userid', 'createtime', 'add_way', 'external_userid', 'type', 'gender'])
    db.close()
    return client_detail_df

def client_detail_groupby(client_table_name):
    # 将客户明细转化为df
    client_detail_df = get_client_detail_df(client_table_name)
    # 计算顾客最早的添加时间
    min_createtime = client_detail_df['createtime'].groupby(client_detail_df['external_userid']).min()
    # 将最早添加时间与顾客明细表合并
    client_detail_new_df = pd.merge(client_detail_df, min_createtime, left_on='external_userid', right_index=True)
    # 为最最早添加的顾客打标
    client_detail_new_df['mark'] = client_detail_new_df.apply(lambda x: 1 if x['createtime_x'] == x['createtime_y'] else 0, axis=1)
    print(client_detail_new_df)
    # 计算各员工顾客总数与去重顾客数、企业顾客与个人顾客，添加方式
    member_client_onlycount = \
    client_detail_new_df.pivot_table(index='userid', columns='mark', aggfunc='count', fill_value=0, margins=True)[
        'createtime_x']
    member_client_type_count = \
    client_detail_new_df.pivot_table(index='userid', columns='type', aggfunc='count', fill_value=0)['createtime_x']
    member_client_addway_count = \
    client_detail_new_df.pivot_table(index='userid', columns='add_way', aggfunc='count', fill_value=0)['createtime_x']
    member_client_gender_count = \
    client_detail_new_df.pivot_table(index='userid', columns='gender', aggfunc='count', fill_value=0)['createtime_x']
    member_count = pd.merge(
        pd.merge(pd.merge(member_client_onlycount, member_client_type_count, left_index=True, right_index=True),
                 member_client_addway_count, left_index=True, right_index=True), member_client_gender_count,
        left_index=True, right_index=True)
    # 更换列名
    #column_name_new = ['重复顾客数', '不重复顾客数', '顾客总数', '个人顾客数', '企业顾客数', '未知添加方式1', '未知添加方式2', '扫描二维码', '搜索手机号', '内部成员共享','管理员分配', '名片共享', '群聊', '微信联系人', '未知性别', '男', '女']
    #member_count.columns = column_name_new
    print(member_count)
    # 输出为excel表格
    with pd.ExcelWriter('D:\member_count_chongqing%s.xlsx' % (strftime('%Y%m%d%H%M%S', localtime(time())))) as writer:
        member_count.to_excel(writer, sheet_name='到人明细')
    return member_count

clean_client_detail_list()
get_client_list_threading_task()
client_detail_groupby("client_detail_list_chongqing")



